package com.bojx.report.service;

import java.util.ArrayList;
import java.util.List;
import org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import com.bojx.report.dao.QuarterReportDao;
import com.bojx.report.pojo.Constant;
import com.jfinal.aop.Before;
import com.jfinal.plugin.activerecord.Db;
import com.jfinal.plugin.activerecord.Record;
import com.jfinal.plugin.activerecord.tx.Tx;
import com.jfinal.upload.UploadFile;

public class ExcelUploadService {
	private QuarterValidation validation=new QuarterValidation();
	private QuarterReportDao quarterReportDao=new QuarterReportDao();
	
    @Before(Tx.class)
	public void addQuarterReport(UploadFile uploadFile, String bank, Integer year, Integer quarter) throws Exception {
	    Record excelRecord=quarterReportDao.queryUploadExcel(bank, year, quarter);
	    if(excelRecord!=null){
	    	throw new Exception("已经上传过了");
	    }
    	
    	/***
		 * 1.校验表格格式
		 * 2.读取数据
		 * 3.存入数据库
		 */
    	Workbook wb = WorkbookFactory.create(uploadFile.getFile());
		HSSFFormulaEvaluator.evaluateAllFormulaCells(wb);//计算出公式的值
		//验证数据
		validation.validate(wb,bank,year,quarter);
		//读取数据
		int rowNum=quarterReportDao.queryRowNum(bank);
		List<Record> businessDara=new ArrayList<>();
		List<Record> branchList=new ArrayList<>();
		List<Record> clientList=new ArrayList<>();
		for(int i=0;i<6;i++){
			Sheet sheet=wb.getSheetAt(i);
			String area=sheet.getSheetName();
			Row row =sheet.getRow(rowNum-1);
			//银行网点情况
			int branchQty=(int) row.getCell(1).getNumericCellValue();
			int edraftBranchQty=(int) row.getCell(2).getNumericCellValue();
			Record branchRecord=new Record();
			branchRecord.set("area", area);
			branchRecord.set("bank", bank);
			branchRecord.set("year", year);
			branchRecord.set("quarter", quarter);
			branchRecord.set("branch_qty", branchQty);
			branchRecord.set("edraft_branch_qty", edraftBranchQty);
			branchList.add(branchRecord);
			//客户情况
			int euserQty=(int) row.getCell(3).getNumericCellValue();
			int ebankEuserQty=(int) row.getCell(4).getNumericCellValue();
			int draftEuserQty=(int) row.getCell(5).getNumericCellValue();
			int edraftEuserQty=(int) row.getCell(6).getNumericCellValue();
			Record clientRecord=new Record();
			clientRecord.set("area", area);
			clientRecord.set("bank", bank);
			clientRecord.set("year", year);
			clientRecord.set("quarter", quarter);
			clientRecord.set("euser_qty", euserQty);
			clientRecord.set("ebank_euser_qty", ebankEuserQty);
			clientRecord.set("draft_euser_qty", draftEuserQty);
			clientRecord.set("edraft_euser_qty", edraftEuserQty);
			clientList.add(clientRecord);
			//电子银行承兑汇票承兑
		    //baEdraftAccept
			List<Record> baEdraftAcceptList=
					getBusinessData(row, bank, area, Constant.baEdraftAccept, year, quarter);
			businessDara.addAll(baEdraftAcceptList)	;
			//电子银行承兑汇票贴现
			//baEdraftDiscount
			List<Record> baEdraftDiscountList=
					getBusinessData(row, bank, area, Constant.baEdraftDiscount, year, quarter);
			businessDara.addAll(baEdraftDiscountList);
			//纸质银行承兑汇票承兑
			//baDraftAccept
			List<Record> baDraftAcceptList=
					getBusinessData(row, bank, area, Constant.baDraftAccept, year, quarter);
			businessDara.addAll(baDraftAcceptList);
			//纸质银行承兑汇票贴现
			//baDraftDiscount
			List<Record> baDraftDiscountList=
					getBusinessData(row, bank, area, Constant.baDraftDiscount, year, quarter);
			businessDara.addAll(baDraftDiscountList);
			//电子商业承兑汇票贴现
			//caEdraftDiscount
			List<Record> caEdraftDiscountList=
					getBusinessData(row, bank, area, Constant.caEdraftDiscount, year, quarter);
			businessDara.addAll(caEdraftDiscountList);
			//纸质商业承兑汇票贴现
			//caDraftDiscount
			List<Record> caDraftDiscountList=
					getBusinessData(row, bank, area, Constant.caDraftDiscount, year, quarter);
			businessDara.addAll(caDraftDiscountList);
			//商业汇票再贴现
			//draftRediscount
			List<Record> draftRediscountList=
					getBusinessData(row, bank, area, Constant.draftRediscount, year, quarter);
			businessDara.addAll(draftRediscountList);
		
		}
		
		Record uploadExcel=new Record();
		uploadExcel.set("bank", bank);
		uploadExcel.set("year", year);
		uploadExcel.set("quarter", quarter);
		Db.save("report_quarter_uploaded_excel", uploadExcel);
	    Db.batchSave("report_quarter_bank_branch_data", branchList, 100);
		Db.batchSave("report_quarter_business_data",businessDara,100 );
		Db.batchSave("report_quarter_client_data", clientList,100);
	}
    
    private List<Record> getBusinessData(Row row,String bank,String area,String billType,int year,int quarter){
    	List<Record> list=new ArrayList<Record>();
    	int[] cellNums=null;
        if(billType.equals(Constant.baEdraftAccept)){
        	cellNums=new int[]{7,8,9,10,11,12,13,14};
        }else if(billType.equals(Constant.baEdraftDiscount)){
        	cellNums=new int[]{15,16,17,18,19,20,21,22};
        }else if(billType.equals(Constant.baDraftAccept)){
        	cellNums=new int[]{23,24,25,26,27,28,29,30};
        }else if(billType.equals(Constant.baDraftDiscount)){
        	cellNums=new int[]{31,32,33,34,35,36,37,38};
        }else if(billType.equals(Constant.caEdraftDiscount)){
        	cellNums=new int[]{39,40,41,42,43,44,45,46};
        }else if(billType.equals(Constant.caDraftDiscount)){
        	cellNums=new int[]{39,40,41,42,43,44,45,46};
        }else if(billType.equals(Constant.draftRediscount)){
        	cellNums=new int[]{47,48,49,50,51,52,53,54};
        }
    	//1.50<x
    	int less50Qty=(int) row.getCell(cellNums[0]).getNumericCellValue();
		double less50Amt=row.getCell(cellNums[1]).getNumericCellValue();
		Record less50Record=new Record();
		less50Record.set("area", area);
		less50Record.set("bank", bank);
		less50Record.set("year", year);
		less50Record.set("quarter", quarter);
		less50Record.set("bill_type", billType);
		less50Record.set("scope", 1);
		less50Record.set("qty", less50Qty);
		less50Record.set("amt", less50Amt);
		list.add(less50Record);
		//2.50<=x<100
		int bet50To100Qty=(int) row.getCell(cellNums[2]).getNumericCellValue();
		double bet50To100Amt=row.getCell(cellNums[3]).getNumericCellValue();
		Record bet50To100Record=new Record();
		bet50To100Record.set("area", area);
		bet50To100Record.set("bank", bank);
		bet50To100Record.set("year", year);
		bet50To100Record.set("quarter", quarter);
		bet50To100Record.set("bill_type", billType);
		bet50To100Record.set("scope", 2);
		bet50To100Record.set("qty", bet50To100Qty);
		bet50To100Record.set("amt", bet50To100Amt);
		list.add(bet50To100Record);
		//3.100<=x<300
		int bet100To300Qty=(int) row.getCell(cellNums[4]).getNumericCellValue();
		double bet100To300Amt=row.getCell(cellNums[5]).getNumericCellValue();
		Record bet100To300Record=new Record();
		bet100To300Record.set("area", area);
		bet100To300Record.set("bank", bank);
		bet100To300Record.set("year", year);
		bet100To300Record.set("quarter", quarter);
		bet100To300Record.set("bill_type", billType);
		bet100To300Record.set("scope", 3);
		bet100To300Record.set("qty", bet100To300Qty);
		bet100To300Record.set("amt", bet100To300Amt);
		list.add(bet100To300Record);
		//4.x>300
		int gt300Qty=(int) row.getCell(cellNums[6]).getNumericCellValue();
		double gt300Amt=row.getCell(cellNums[7]).getNumericCellValue();
		Record gt300QRecord=new Record();
		gt300QRecord.set("area", area);
		gt300QRecord.set("bank", bank);
		gt300QRecord.set("year", year);
		gt300QRecord.set("quarter", quarter);
		gt300QRecord.set("bill_type", billType);
		gt300QRecord.set("scope", 4);
		gt300QRecord.set("qty", gt300Qty);
		gt300QRecord.set("amt", gt300Amt);
		list.add(gt300QRecord);
		return list;
		
    }
   
    
}
